package com.bean.ywj;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TitleDaoImpl implements TitleDao {
	private Connection conn;
	private PreparedStatement titlesQuery;
	private ResultSet rs;

	@Override
	public List<Title> getTitles() {
		List<Title> titlesList=new ArrayList<Title>();
		try {
		conn=DBcon.getConnection();
		String sql="select isbn,title,editionNumber,copyright,publisherID,imageFile,price from titles order by title";
		titlesQuery=conn.prepareStatement(sql);
		ResultSet rs=titlesQuery.executeQuery();
		while(rs.next()) {
			Title book=new Title();
			book.setIsbn(rs.getString("isbn"));
			book.setTitle(rs.getString("title"));
			book.setEditionNumber(rs.getInt("editionNumber"));
			book.setCopyright(rs.getString("copyright"));
			book.setPublisherId(rs.getInt("publisherID"));
			book.setImageFile(rs.getString("imageFile"));
			book.setPrice(rs.getDouble("price"));
			titlesList.add(book);
		}
		}catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			DBcon.closeResult(rs);
			DBcon.closeStatement(titlesQuery);
			DBcon.closeConnection(conn);
		}
		return titlesList;
	}

	@Override
	public int add(Title titlebean) {
		int result=0;
		try {
		conn=DBcon.getConnection();
		String sql="insert into titles values(?,?,?,?,?,?,?)";
			titlesQuery=conn.prepareStatement(sql);
			titlesQuery.setString(1, titlebean.getIsbn());
			titlesQuery.setString(2,titlebean.getTitle());
			titlesQuery.setInt(3,titlebean.getEditionNumber() );
			titlesQuery.setString(4,titlebean.getCopyright());
			titlesQuery.setInt(5,titlebean.getPublisherId());
			titlesQuery.setString(6,titlebean.getImageFile());
			titlesQuery.setDouble(7,titlebean.getPrice());
			result=titlesQuery.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			DBcon.closeResult(rs);
			DBcon.closeStatement(titlesQuery);
			DBcon.closeConnection(conn);
		}
		
		return result;
	}

	@Override
	public int delete(String isbn) {
		int result=0;
		try {
			conn=DBcon.getConnection();
			String sql="delete from titles where isbn='"+isbn+"'";
			titlesQuery=conn.prepareStatement(sql);
			result=titlesQuery.executeUpdate();
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			DBcon.closeResult(rs);
			DBcon.closeStatement(titlesQuery);
			DBcon.closeConnection(conn);
		}
		
		return result;
	}

	@Override
	public int update(Title titlebean) {
		int result=0;
		try {
			conn=DBcon.getConnection();
			String sql="update titles set title=?,editionNumber=?,copyright=?,publisherID=?,imageFile=?,price=? where isbn=?";
			titlesQuery=conn.prepareStatement(sql);
			titlesQuery.setString(1,titlebean.getTitle());
			titlesQuery.setInt(2, titlebean.getEditionNumber());
			titlesQuery.setString(3,titlebean.getCopyright());
			titlesQuery.setInt(4,titlebean.getPublisherId());
			titlesQuery.setString(5,titlebean.getImageFile());
			titlesQuery.setDouble(6,titlebean.getPrice());
			titlesQuery.setString(7,titlebean.getIsbn());
			result=titlesQuery.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			DBcon.closeResult(rs);
			DBcon.closeStatement(titlesQuery);
			DBcon.closeConnection(conn);
		}
		
		return result;
	}

	@Override
	public Title findByIsbn(String isbn) {
		Title book=null;
		try {
			conn=DBcon.getConnection();
			String sql="select * from titles where isbn='"+isbn+"'";
			titlesQuery=conn.prepareStatement(sql);
			rs=titlesQuery.executeQuery();
			if(rs.next()) {
				book=new Title();
				book.setIsbn(rs.getString("isbn"));
				book.setTitle(rs.getString("title"));
				book.setEditionNumber(rs.getInt("editionNumber"));
				book.setCopyright(rs.getString("copyright"));
				book.setPublisherId(rs.getInt("publisherID"));
				book.setImageFile(rs.getString("imageFile"));
				book.setPrice(rs.getDouble("price"));
				
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			DBcon.closeResult(rs);
			DBcon.closeStatement(titlesQuery);
			DBcon.closeConnection(conn);
		}
		
		return book;
	}

}
